import pymysql
import redis

DBHOST = 'localhost'
DBUSER = 'root'
PASSWORD = 'kongxingli2002'
DBNAME = 'supermarket'

search_keys = {
    "员工": ["员工编号", "员工姓名", "员工密码", "员工级别", "员工电话", "员工工资", "备注"],
    "供应商": ["供应商编号", "供应商名称", "供应商简称", "地址", "公司电话", "邮件", "联系人电话", "备注"],
    "商品": ["商品编号", "商品名称", "商品单价", "供应商编号", "商品简介", "备注"],
    "采购主表": ["采购清单号", "员工编号", "采购数量", "采购总价", "采购时间", "备注"],
    "采购明细": ["采购明细号", "采购清单号", "商品编号", "采购数量", "商品单价", "商品总价", "备注"],
    "users":["账户名","密码"]
}#表：[主码,...]

class Connect:
    def __init__(self):
        try:
            #连接数据库
            self.db = pymysql.connect(host=DBHOST, user=DBUSER, password=PASSWORD, database=DBNAME)
            #连接redis
            self.redis_db = redis.StrictRedis(host='localhost', port=6379, db=0, decode_responses=True)
        except pymysql.Error as e:
            self.db = None
            self.redis_db = None

    def insert_with_trigger_check(self, table, data):
        if not self.db:
            return False, "数据库连接失败"

        try:
            with self.db.cursor() as cur:
                # 检查主码是否已存在
                primary_key = search_keys.get(table, [])[0]  # 获取表对应的主码字段名
                if primary_key in data:
                    primary_value = data[primary_key]
                    check_sql = f"SELECT 1 FROM {table} WHERE {primary_key} = %s"
                    cur.execute(check_sql, (primary_value,))
                    if cur.fetchone():
                        return False, "数据已存在"

                # 执行插入操作
                columns = ','.join(data.keys())
                columns_values = ','.join(['%s'] * len(data))
                values = tuple(data.values())
                sql = f'INSERT INTO {table}({columns}) VALUES ({columns_values})'
                cur.execute(sql, values)
                self.db.commit()
                return True, "插入成功！"
        except pymysql.Error as e:
            error_message = str(e)
            if "商品单价必须大于0" in error_message:
                return False, "商品单价必须大于0，请重新输入"
            elif "采购数量必须大于0" in error_message:
                return False, "采购数量必须大于0，请重新输入"
            else:
                return False, f"Database error: {error_message}"
    def select(self, table):
        if not self.db:
            #表空
            return []
        try:
            with self.db.cursor() as cur:
                sql = f'SELECT * FROM {table}'
                cur.execute(sql)
                results = cur.fetchall()
                return results
        except pymysql.Error as e:
            return []

    def update(self, table, set_values, condition_value):
        condition = search_keys[table]
        if not self.db:
            return
        try:
            with self.db.cursor() as cur:
                set_clause = ','.join([f"{key}=%s" for key in set_values.keys()])
                sql = f'UPDATE {table} SET {set_clause} WHERE {condition[0]}=%s'
                values = list(set_values.values())
                values.append(condition_value)
                cur.execute(sql, values)
                self.db.commit()
        except pymysql.Error as e:
            self.db.rollback()

    def search_data(self, table, search_value):
        search_key = search_keys[table][0]  # Assuming the first key is the primary search key
        if not self.db:
            return False, []

        try:
            with self.db.cursor() as cur:
                sql = f'SELECT * FROM {table} WHERE {search_key}=%s'
                cur.execute(sql, (search_value,))
                result = cur.fetchall()
                if result:
                    return True, result
                else:
                    return False, result
        except pymysql.Error as e:
            self.db.rollback()
            return False, []

    def delete(self, table, search_value):
        if not self.db:
            return

        try:
            with self.db.cursor() as cur:
                # 检查是否存在于采购明细表中
                if table == "商品":
                    check_sql = f"SELECT 1 FROM 采购明细 WHERE 商品编号 = %s"
                    cur.execute(check_sql, (search_value,))
                    if cur.fetchone():
                        return False, "该商品已存在于采购明细表中，无法删除"

                # 继续删除操作
                search_key = search_keys[table][0]  # Assuming the first key is the primary search key
                sql = f'DELETE FROM {table} WHERE {search_key}=%s'
                cur.execute(sql, (search_value,))
                self.db.commit()
                return True, "删除成功"
        except pymysql.Error as e:
            self.db.rollback()
            return False, f"数据库错误: {str(e)}"

    def check(self,max_number,table):       #max_number最大数，table表名
        if not self.db:
            return

        try:
            with self.db.cursor() as cur:
                sql = f"SELECT * FROM {table} WHERE 采购数量 <= %s"
                cur.execute(sql, (max_number,))
                rows = cur.fetchall()

                if not rows:
                    return False,f"该要求之内无数据"

                return True,rows
        except self.db.Error as e:
            return False, f"数据库错误: {str(e)}"



    def __del__(self):
        if self.db:
            self.db.close()
